Format Registry entry (field specific)

Specify two separate features on a per field basis: case-conversion, and uniqueness checking and auto-incrementation. A field may use one or the other feature, not both. Which functionality is invoked depends on the format string entered.

Be aware that this field-specific Registry entry is distinct from the Format Registry entry (System-wide) which is for setting global formats on date and time fields.

With this variation of the Format Registry entry it is possible to specify automatic case-conversion of data on a per column basis.

Any column in Vitalware can be configured for automatic case conversion so that regardless of the case of the data entered into a field, it will display in the case format specified by the Registry entry for that field.

Note: The conversion occurs when the user exits a field. Case conversion does not effect values entered in fields in Search mode.

Usage

Key User User Group Group Group Group
Key 1 User User Group Group Group Group
Key 2 user user group group Default Default
Key 3 Table Table Table Table Table Table
Key 4 table Default table Default table Default
Key 5 Format
Key 6 column or Default
Value format
User user Table table Format column format
User user Table Default Format column format
Group group Table table Format column format
Group group Table Default Format column format
Group Default Table table Format column format
Group Default Table Default Format column format
User user Table table Format Default format
User user Table Default Format Default format
Group group Table table Format Default format
Group group Table Default Format Default format
Group Default Table table Format Default format
Group Default Table Default Format Default format

where:

column

is a back-end column name; or use Default for all columns in the specified table(s).

format

can be one of the following values:
uc

Convert input to UPPER CASE, e.g. SYSTEMS MANAGER.
lc

Convert input to lower case, e.g. systems manager.
ucfirst

The first letter should be UPPER CASE, all other letters lower case, e.g. Systems manager.
ucword

The first letter of each word should be UPPER CASE and the rest lower case, e.g. Systems Manager.

Proper nouns should have the first letter UPPER CASE and the remaining letters lower case:

Field Setting
Key 1 Group
Key 2 Default
Key 3 Table
Key 4 eparties
Key 5 Format
Key 6 NamFirst
Value ucfirst

After typing john into the First: (Person Details) field in the Parties module and leaving that field, the value will be converted to John.

Record status in all tables should appear as UPPER CASE:

Field Setting
Key 1 Group
Key 2 Default
Key 3 Table
Key 4 Default
Key 5 Format
Key 6 SecRecordStatus
Value uc

All fields in all databases should be lower case:

Field Setting
Key 1 Group
Key 2 Default
Key 3 Table
Key 4 Default
Key 5 Format
Key 6 Default
Value lc

With this variation of the Format Registry entry it is possible to specify uniqueness checking and auto-incrementation of values on a per column basis. Auto-incrementation is applied to a column that has one or more rolling values, for example a column that displays the next available number when a new record is inserted.

Notes:

  • If default values are not specified for a column with an incrementing number, a value will not display automatically when a new record is created. It is necessary to trigger the auto-incrementation and display of the number. In other words, the field will remain blank until the auto-incrementation of the number is triggered.

    Note: If the Format Registry entry only specifies an auto-incrementing number in a column, to trigger the display and auto-incrementation of the number: enter any non-digit character that is acceptable in a number field e.g. + or - and press TAB to exit the field. The next available number will display.
    See Example 1 for details.

  • Non-incrementing components included in a format (e.g. %s in %s.%1^d or TEXT in TEXT.%1^d) must be entered into the field before the incrementing component is calculated. In this example, if no default value for %s has been specified for the field, the user would need to enter text in the field and press TAB before the auto-incremented number (%1^d) would be calculated. The same would be true if TEXT.%1^d is specified as the format. In this case, the user would have to key the word TEXT into the field before pressing TAB. This would exit the field and trigger the calculation and display of the auto-incremented number.

    See Example 3 for details.

    Note: By specifying an Insert Defaults Registry entry however, an auto-incremented value can display automatically when a new record is created.

Any column for which uniqueness checking and auto-incrementation is to be enabled MUST have the Unique attribute specified:

  • In versions of Vitalware prior to 2.3 the unique attribute is set in the table schema itself (typically by the developer). Contact Vitalware Support for details.

    Once the Unique attribute has been set on a field, a re-index is required.

  • In versions of Vitalware 2.3 onwards the Unique attribute can be set using the Unique Index Indexing Registry entry.
Key User User Group Group Group Group
Key 1 User User Group Group Group Group
Key 2 user user group group Default Default
Key 3 Table Table Table Table Table Table
Key 4 table Default table Default table Default
Key 5 Format
Key 6 column
Value format
User user Table table Format column format
User user Table Default Format column format
Group group Table table Format column format
Group group Table Default Format column format
Group Default Table table Format column format
Group Default Table Default Format column format

where:

format

is constructed using the following values:
%s

Text
%d

Number

To specify that the number is two digits:

%2d

To specify that the number is two digits padded with 0 for any single digit (i.e. 07):

%02d

To specify that the number is three digits padded with 0 (i.e. 001, 099):

%03d
%Y

Year

If not supplied, this defaults to four digit current year.
%n^d

Incrementing number where n specifies the order in which these incrementing components are incremented. Typically there is only one incrementing component in a format string, so this is usually:

%1^d

If there are two incrementing components, they are:

%1^d and %2^d

For example: %1^03d and %2^03d

%1^03d is a three digit incrementing number in the range 001 to 999. When the number reaches 999, %2^03d will begin to increment from 001 to 999.
%@n^d

Use of @ specifies that this is an optional incrementing component. It can only be used when there is more than one incrementing number in a format string (e.g. %s-%1^d-%2^d) and only on the least significant incrementing number (i.e. never on %1^d, but it could be used in %2^d or if there were three incrementing numbers, on the third).
%&

Column separator specifying that the following components apply to the next column in the chain.

Note: The format syntax is similar to C/C++/Perl printf style syntax with some restrictions and some extensions.

An institution wants the Birth Registration Number (BthRegNo) in the Births module to be unique and auto-incremented. The format for the Birth Registration Number is:

  1. A text prefix: BTH.
  2. The current year.
  3. An auto-incremented number.
  4. Each component separated by a hyphen (-), e.g. BTH-2014-1

The format specified is thus: %s-%Y-%1^d

When a user enters a value in the Birth Registration Number field, it is sent to the back-end which separates it into the various components, determines which is the next immediate component to be incremented, increments that component to provide the next unique value, and finally reformats the value according to the format string.

Note: For multi incrementing formats (e.g. %1^d-%2^d) the user will need to revisit the field to trigger incrementation of the next component.

Values for non-incrementing components (e.g. text) must be provided by the user or, as we'll see in Example 1, an Insert Defaults Registry entry. It is not necessary to include values for known components, e.g. the current year if %Y is used. (Of course, if you want to enter a number for a previous year, it would be necessary to enter that year).

Punctuation is ignored and superfluous punctuation is discarded once the value is reformatted.

Assuming a record has an Birth Registration Number of BTH-2014-1:

If a user entered:

Result
BTH -2014

A value of BTH-2014-2 would be returned.

BTH -2014-1

An error message would display warning that the value was not unique.
BTH

A value of BTH-2014-2 would be returned. %Y is a known value (the current year) and will be completed automatically.

BTH -2014-5

If a full value is entered, incrementation is not attempted, however uniqueness checking is performed.

Click the example format for details:

%1^d

An auto-incremented number.

%s-%Y-%1^d

Three hyphen separated components; a leading prefix, a numeric (a four digit year), and finally an auto-incrementing number.

PRE.%s.123.%1^d.SUF

A custom format to illustrate what is possible. The explicit text PRE, followed by a text value, then the explicit number 123, then an incrementing number and finally the explicit text SUF, all separated by full stops (periods).

Various

%1^d-%s-%2^d-%@3^d

%s%&%1^d

%02d.%1^04d

%02d.%1^04d.%@2^d

An institution wants the Application Number (SalCorrespondenceNumber) in the POS module (epos) to be unique and auto-incremented.

In this example the format for the Application Number is simply an auto-incremented number (1%^d).

If the Unique attribute has not been set in the back-end on the SalCorrespondenceNumber field, set it:

System|Setting|Table| epos |Unique Index|SalCorrespondenceNumber

Specify the Format Registry entry for this same field:

Group|Default|Table|epos|Format|SalCorrespondenceNumber|%1^d

Note: In practice, Application Number is a read-only field and the number is always auto-generated: the next available number always displays automatically without the need for a user to trigger its display. For this example, in order to demonstrate the triggering process, we assume that the user is able to trigger the auto-incrementation and display of the number.

Next time aPOS record is created, and the next Application Number is unknown:

  1. Enter + in the Application Number field.
  2. Press TAB to move to the next field.

    The next available number in the Application Number field will be determined and will display:

    Note: If the format includes two auto-incrementing numbers, e.g. %1^d.%2^d, enter + in the field and press TAB to move to the next field. The first incremented number is calculated. Return to the field and enter + after the first incremented number and press TAB. The second incremented number is calculated.

An institution wants the Application Number (SalCorrespondenceNumber ) in the POS module to be unique and auto incremented. The Application Number comprises three hyphen separated components: a leading prefix, a numeric (a four digit year), and an auto-incrementing number:

  • A text prefix: App (%s)
  • The current year (%Y)
  • An auto-incremented number (%1^d)

The format for this value is: %s-%Y-%1^d

In the POS module, the value will display as: App-2014-1

If the Unique attribute has not been set in the back-end on the SalCorrespondenceNumber field, set it:

System|Setting|Table| epos |Unique Index|SalCorrespondenceNumber

  1. Specify the Format Registry entry for this same field:

    Group|Default|Table|epos|Format|SalCorrespondenceNumber|%s-%Y-%1^d

Imagine that there are already three Pos records with the following Application Numbers:

  • App -2014-1
  • App-2014-2
  • App-2014-3

Consider these scenarios:

A user types the following into the Application Number field in the POS module:

When the user moves to the next field:

App

The value in the Application Number field will automatically change to:

App-2014-4

App-2014

The value in the Application Number field will automatically change to:

App-2014-4

App-2014-3

An error message is generated indicating that the Application Number must be unique.

To fully automate the display of the Application number, we could:

  1. Specify an Insert Defaults Registry entry (for all users, or perhaps for specific groups, each with their own Prefix). In this case, the entry is specified for all users:

    Group|Default|Table|epos|Insert Defaults|Application Number|SalCorrespondenceNumber=App

  2. The set of default values called Application Number must be active for the user / group (or all groups by default) - users can select it in the Defaults box:

    or an Insert Defaults Selected Registry entry could be specified by the Administrator.

    Now, whenever a new record is created in the POS module, the default value (App) will automatically display in the Application Number field. This will trigger the Format Registry entry and the remainder of the Application Number (the year and auto-incremented number) will automatically display:

This example is a more complex illustration of what is possible with the Format Registry entry:

PRE.%s.123.%1^d.SUF

The text PRE

  • Is followed by a text value
  • Followed by the number 123
  • Followed by an incrementing number
  • Concluding with the text SUF
  • All separated by full stops (periods)

If this entry was intended to format the Application Number in the POS module, a Registry entry suitable for all users would be:

Group|Default|Table|epos|Format|SalCorrespondenceNumber|PRE.%s.123.%1^d.SUF

This example extends the previous.

Consider the following format for Application Number in the POS module:

%s-%s-%Y-%1^03d-Application

  • %s is a department name
  • The second %s is a department name
  • %Y is the current year
  • %1^03d is an incrementing number from 001 to 999
  • Application is a text suffix
  • All separated by dashes

A suitable Registry entry would be:

Group|Default|Table| epos |Format| SalCorrespondenceNumber |%s-%s-%Y-%1^03d-Application

If no Insert Defaults Registry entry is specified, a user would need to enter two bits of text (separated with a space or some form of punctuation), e.g.

Acquisitions Managers (or Counter Sales (or Counter.Sales or Counter-Sales, etc.). Pressing TAB will trigger the Format entry to calculate the date, increment the number, add the text suffix and format the entire string with dashes.

  1. To fully automate the process however, we could specify an Insert Defaults Registry entry for each group.

    For example, for Counter staff in the Sales department we could specify the following:

    Group|Managers|Table|epos|Insert Defaults|Counter Sales|TitAccessionNo=Counter-Sales

  2. We could also specify an Insert Defaults Selected Registry entry for the Counter group:

    Group|Managers|Table|epos|Insert Defaults Selected|Counter Sales

If we already have a POS record with an Application Number up to:

Counter-Sales-2014-001-Application

when a Counter staff member in the Sales Department next creates a record in the POS module, the Application Number field will be populated automatically as follows:

What takes place?:

The Insert Defaults Registry entry for the Counter group automatically populates the Application Number field with the value:

Counter-Sales

This triggers the calculation and display of the remainder of the value: the Year is provided, followed by an incremented number, followed by the suffix Application, with dashes separating each component of the value.

%1^d-%s-%2^d-%@3^d

Four components separated by the hyphen character. The first (most significant) incrementing component, followed by a text value, the next incrementing component and finally the last (least significant) incrementing component.

The final incrementing component uses the @ character to specify that this component is optional.

Hence for this format the value 1-A-2 is valid; it would not be valid if the @ character was not specified.

%s%&%1^d

A two column format. The first column is text, the second column is an incrementing number.

%02d.%1^04d

A valid number is one in which:

  1. The first component consists of a two digit number (01 to 99).
  2. Followed by a full stop (period).
  3. And the second component is an auto incrementing four digit number (0001 to 9999).

%02d.%1^04d.%@2^d

Extends the previous example. A number is valid if an optional incrementing component is added.

%Y-%1^03d%2^03d

This example includes two incrementing numbers. For the System to calculate both numbers:

  1. Enter + in the field and press TAB to exit the field.

    The first incrementing number is calculated.

  2. Return to the field and enter + after the first incremented number and press TAB to exit the field.

    The second incrementing number is calculated.